<!DOCTYPE html>
<html>
<head><meta name="generator" content="Hexo 3.8.0">
  <meta charset="utf-8">
  
  <title>MySQL 8.0 Optimizer | MyWeb詹</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  <meta name="description" content="最近在看《高性能 MySQL》, 顺便把 MySQL 8.0 的一些内容补充一下, 以下内容主要都是 optimizer_switch 中新添加的三个优化 隐藏索引(invisible index)用法:1ALTER TABLE t1 ALTER INDEX i_idx &amp;#123;INVISIBLE | VISIBLE&amp;#125;; 变量: optimizer_switch=&amp;quot;use_">
<meta name="keywords" content="mysql">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL 8.0 Optimizer">
<meta property="og:url" content="https://github.com/rarpainting/2019/10/31/page/index.html">
<meta property="og:site_name" content="MyWeb詹">
<meta property="og:description" content="最近在看《高性能 MySQL》, 顺便把 MySQL 8.0 的一些内容补充一下, 以下内容主要都是 optimizer_switch 中新添加的三个优化 隐藏索引(invisible index)用法:1ALTER TABLE t1 ALTER INDEX i_idx &amp;#123;INVISIBLE | VISIBLE&amp;#125;; 变量: optimizer_switch=&amp;quot;use_">
<meta property="og:locale" content="default">
<meta property="og:image" content="https://github.com/custom/img/1121108860.jpg">
<meta property="og:updated_time" content="2019-11-13T13:47:59.575Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="MySQL 8.0 Optimizer">
<meta name="twitter:description" content="最近在看《高性能 MySQL》, 顺便把 MySQL 8.0 的一些内容补充一下, 以下内容主要都是 optimizer_switch 中新添加的三个优化 隐藏索引(invisible index)用法:1ALTER TABLE t1 ALTER INDEX i_idx &amp;#123;INVISIBLE | VISIBLE&amp;#125;; 变量: optimizer_switch=&amp;quot;use_">
<meta name="twitter:image" content="https://github.com/custom/img/1121108860.jpg">
  
  <link rel="stylesheet" href="//cdn.bootcss.com/highlight.js/9.2.0/styles/github.min.css">
  <script src="//cdn.bootcss.com/highlight.js/9.2.0/highlight.min.js"></script>
  <script>
    document.addEventListener('DOMContentLoaded', (event) => {
      document.querySelectorAll('pre code').forEach((block) => {
        hljs.highlightBlock(block);
      });
    });
  </script>
  <link rel="stylesheet" href="/css/index.css">
</head>
</html>
<body style="


  background-color: #eff0f6

">
  <div id="container">
    <nav id="nav">
  <header class="header">
    <a href="/" class="title">Rarpainting</a>
  </header>
  <div class="ctnWrap">
    <div class="icons">
      
        
          
            <a href="https://github.com/rarpainting" target="_blank" class="nav-icn iconfont icon-github"></a>
          
        
      
    </div>
    <div class="menu">
      
        
            <a href="/" class="nav-menu ">HOME</a>
          
        
            <a href="/archives" class="nav-menu ">ARCHIVE</a>
          
        
            <a href="/about" class="nav-menu ">ABOUT</a>
          
        
      
    </div>
  </div>
</nav>
    <div id="main"><section class="article">
  <h2 class="title">MySQL 8.0 Optimizer</h2>
  <p class="sub">Oct 31, 2019</p>
  <article class="content">
    <p>最近在看《高性能 MySQL》, 顺便把 MySQL 8.0 的一些内容补充一下, 以下内容主要都是 <code>optimizer_switch</code> 中新添加的三个优化</p>
<h2 id="隐藏索引-invisible-index"><a href="#隐藏索引-invisible-index" class="headerlink" title="隐藏索引(invisible index)"></a>隐藏索引(invisible index)</h2><p>用法:<br><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> t1 <span class="keyword">ALTER</span> <span class="keyword">INDEX</span> i_idx &#123;<span class="keyword">INVISIBLE</span> | <span class="keyword">VISIBLE</span>&#125;;</span><br></pre></td></tr></table></figure></p>
<p>变量: <code>optimizer_switch=&quot;use_invisible_indexes=off&quot;</code></p>
<p>隐藏索引更像是作为工程功能为灰度测试服务, 本身不作为实际的索引效果</p>
<p>但是作为测试功能, 隐藏索引能够作为一种软删除机制, 避免大表重建索引, 还是相当值得肯定的; 当然, 隐藏的唯一索引依然具有阻止重复的功能</p>
<h2 id="Skip-Scan"><a href="#Skip-Scan" class="headerlink" title="Skip Scan"></a>Skip Scan</h2><h3 id="简介"><a href="#简介" class="headerlink" title="简介"></a>简介</h3><p>查询中使用复合索引, 但是却没有使用上前缀索引, 这种情况在 8.0 之前会直接 full-table-scans</p>
<p>在 8.0.13 之后, 开启 <code>optimizer_switch=&quot;skip_scan=on&quot;</code></p>
<p>以上的情况则会触发 Skip-Scan 的功能, 即 MySQL 根据索引统计, 预估每一个前缀索引块的有效范围, 掠过(skip) 每个前缀索引块起始的部分索引; 值得一提的是, Skip scan 功能就和字符串的区分度类似, 依赖于数据库在统计分析上的准确度, 因此 <strong>适时的 <code>ANALYZE TABLE table_name;</code> 有助于数据库维持统计准确度</strong></p>
<h3 id="配置与使用"><a href="#配置与使用" class="headerlink" title="配置与使用"></a>配置与使用</h3><p>借用 <a href="https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html#range-access-skip-scan" target="_blank" rel="noopener">MySQL 官方案例</a>, 构建以下表:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> t1 (f1 <span class="built_in">INT</span> <span class="keyword">NOT</span> <span class="literal">NULL</span>, f2 <span class="built_in">INT</span> <span class="keyword">NOT</span> <span class="literal">NULL</span>, PRIMARY <span class="keyword">KEY</span>(f1, f2));</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> t1 <span class="keyword">VALUES</span></span><br><span class="line">  (<span class="number">1</span>,<span class="number">1</span>), (<span class="number">1</span>,<span class="number">2</span>), (<span class="number">1</span>,<span class="number">3</span>), (<span class="number">1</span>,<span class="number">4</span>), (<span class="number">1</span>,<span class="number">5</span>),</span><br><span class="line">  (<span class="number">2</span>,<span class="number">1</span>), (<span class="number">2</span>,<span class="number">2</span>), (<span class="number">2</span>,<span class="number">3</span>), (<span class="number">2</span>,<span class="number">4</span>), (<span class="number">2</span>,<span class="number">5</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> t1 <span class="keyword">SELECT</span> f1, f2 + <span class="number">5</span> <span class="keyword">FROM</span> t1;</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> t1 <span class="keyword">SELECT</span> f1, f2 + <span class="number">10</span> <span class="keyword">FROM</span> t1;</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> t1 <span class="keyword">SELECT</span> f1, f2 + <span class="number">20</span> <span class="keyword">FROM</span> t1;</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> t1 <span class="keyword">SELECT</span> f1, f2 + <span class="number">40</span> <span class="keyword">FROM</span> t1;</span><br><span class="line"><span class="keyword">ANALYZE</span> <span class="keyword">TABLE</span> t1;</span><br><span class="line"></span><br><span class="line"><span class="keyword">EXPLAIN</span> <span class="keyword">SELECT</span> f1, f2 <span class="keyword">FROM</span> t1 <span class="keyword">WHERE</span> f2 &gt; <span class="number">40</span>\G</span><br><span class="line"><span class="keyword">EXPLAIN</span> <span class="keyword">ANALYZE</span> <span class="keyword">SELECT</span> f1, f2 <span class="keyword">FROM</span> t1 <span class="keyword">WHERE</span> f2 &gt; <span class="number">40</span>\G</span><br></pre></td></tr></table></figure>
<p>输出:</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><span class="line">***************************[ 1. row ]***************************</span><br><span class="line">id            | 1</span><br><span class="line">select_type   | SIMPLE</span><br><span class="line">table         | tssr</span><br><span class="line">partitions    | &lt;null&gt;</span><br><span class="line">type          | range</span><br><span class="line">possible_keys | PRIMARY</span><br><span class="line">key           | PRIMARY</span><br><span class="line">key_len       | 8</span><br><span class="line">ref           | &lt;null&gt;</span><br><span class="line">rows          | 53</span><br><span class="line">filtered      | 100.0</span><br><span class="line">Extra         | Using where; Using index for skip scan</span><br><span class="line"></span><br><span class="line">1 row in set</span><br><span class="line">Time: 0.009s</span><br><span class="line"></span><br><span class="line">***************************[ 1. row ]***************************</span><br><span class="line">EXPLAIN | -&gt; Filter: (tssr.f2 &gt; 40)  (cost=15.93 rows=53) (actual time=0.039..0.121 rows=80 loops=1)</span><br><span class="line">    -&gt; Index range scan on tssr using index_for_skip_scan(PRIMARY)  (cost=15.93 rows=53) (actual time=0.037..0.109</span><br><span class="line">rows=80 loops=1)</span><br><span class="line"></span><br><span class="line">1 row in set</span><br><span class="line">Time: 0.008s</span><br></pre></td></tr></table></figure>
<h2 id="Hash-Join"><a href="#Hash-Join" class="headerlink" title="Hash Join"></a>Hash Join</h2><h3 id="简介-1"><a href="#简介-1" class="headerlink" title="简介"></a>简介</h3><p>Hash Join, 如你所见, 驱动表和被驱动表在 join_buffer 中不再通过 join_key 配对, 而是通过预先计算的 hash_key</p>
<p>Hash Join 主要用于在 join_key 非索引且存在相等条件连接的场景中, 代替 <a href="https://dev.mysql.com/doc/refman/8.0/en/nested-loop-joins.html#block-nested-loop-join-algorithm" target="_blank" rel="noopener">BNLJ</a></p>
<p>MySQL 8.0.18 后, 开启 <code>optimizer_switch=&quot;hash_join=on&quot;</code></p>
<h3 id="配置与使用-1"><a href="#配置与使用-1" class="headerlink" title="配置与使用"></a>配置与使用</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> t1 (c1 <span class="built_in">INT</span>, c2 <span class="built_in">INT</span>);</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> t2 (c1 <span class="built_in">INT</span>, c2 <span class="built_in">INT</span>);</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> t3 (c1 <span class="built_in">INT</span>, c2 <span class="built_in">INT</span>);</span><br><span class="line"></span><br><span class="line"><span class="keyword">EXPLAIN</span> <span class="keyword">FORMAT</span>=TREE</span><br><span class="line">        <span class="keyword">SELECT</span> *</span><br><span class="line">           <span class="keyword">FROM</span> t1</span><br><span class="line">           <span class="keyword">JOIN</span> t2</span><br><span class="line">               <span class="keyword">ON</span> t1.c1=t2.c1\G</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">*************************** 1. row ***************************</span><br><span class="line">EXPLAIN: -&gt; Inner hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)</span><br><span class="line">    -&gt; Table scan on t2  (cost=0.35 rows=1)</span><br><span class="line">    -&gt; Hash</span><br><span class="line">        -&gt; Table scan on t1  (cost=0.35 rows=1)</span><br></pre></td></tr></table></figure>
<p>在 <strong>主合取范式且外层范式中至少有一个相等条件连接时</strong>, Hash Join 也会触发</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; EXPLAIN FORMAT=TREE</span><br><span class="line">    -&gt; SELECT *</span><br><span class="line">    -&gt;     FROM t1</span><br><span class="line">    -&gt;     JOIN t2</span><br><span class="line">    -&gt;         ON (t1.c1 = t2.c1 AND t1.c2 &lt; t2.c2)</span><br><span class="line">    -&gt;     JOIN t3</span><br><span class="line">    -&gt;         ON (t2.c1 = t3.c1)\G</span><br><span class="line"></span><br><span class="line">*************************** 1. row ***************************</span><br><span class="line"><span class="keyword">EXPLAIN</span>: -&gt; <span class="keyword">Inner</span> <span class="keyword">hash</span> <span class="keyword">join</span> (t3.c1 = t1.c1)  (<span class="keyword">cost</span>=<span class="number">1.05</span> <span class="keyword">rows</span>=<span class="number">1</span>)</span><br><span class="line">    -&gt; <span class="keyword">Table</span> <span class="keyword">scan</span> <span class="keyword">on</span> t3  (<span class="keyword">cost</span>=<span class="number">0.35</span> <span class="keyword">rows</span>=<span class="number">1</span>)</span><br><span class="line">    -&gt; <span class="keyword">Hash</span></span><br><span class="line">        -&gt; Filter: (t1.c2 &lt; t2.c2)  (<span class="keyword">cost</span>=<span class="number">0.70</span> <span class="keyword">rows</span>=<span class="number">1</span>)</span><br><span class="line">            -&gt; <span class="keyword">Inner</span> <span class="keyword">hash</span> <span class="keyword">join</span> (t2.c1 = t1.c1)  (<span class="keyword">cost</span>=<span class="number">0.70</span> <span class="keyword">rows</span>=<span class="number">1</span>)</span><br><span class="line">                -&gt; <span class="keyword">Table</span> <span class="keyword">scan</span> <span class="keyword">on</span> t2  (<span class="keyword">cost</span>=<span class="number">0.35</span> <span class="keyword">rows</span>=<span class="number">1</span>)</span><br><span class="line">                -&gt; <span class="keyword">Hash</span></span><br><span class="line">                    -&gt; <span class="keyword">Table</span> <span class="keyword">scan</span> <span class="keyword">on</span> t1  (<span class="keyword">cost</span>=<span class="number">0.35</span> <span class="keyword">rows</span>=<span class="number">1</span>)</span><br></pre></td></tr></table></figure>
<p>以及单纯的笛卡尔积:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; EXPLAIN FORMAT=TREE</span><br><span class="line">    -&gt; SELECT *</span><br><span class="line">    -&gt;     FROM t1</span><br><span class="line">    -&gt;     JOIN t2</span><br><span class="line">    -&gt;     WHERE t1.c2 &gt; 50\G</span><br><span class="line"></span><br><span class="line">*************************** 1. row ***************************</span><br><span class="line"><span class="keyword">EXPLAIN</span>: -&gt; <span class="keyword">Inner</span> <span class="keyword">hash</span> <span class="keyword">join</span>  (<span class="keyword">cost</span>=<span class="number">0.70</span> <span class="keyword">rows</span>=<span class="number">1</span>)</span><br><span class="line">    -&gt; <span class="keyword">Table</span> <span class="keyword">scan</span> <span class="keyword">on</span> t2  (<span class="keyword">cost</span>=<span class="number">0.35</span> <span class="keyword">rows</span>=<span class="number">1</span>)</span><br><span class="line">    -&gt; <span class="keyword">Hash</span></span><br><span class="line">        -&gt; Filter: (t1.c2 &gt; <span class="number">50</span>)  (<span class="keyword">cost</span>=<span class="number">0.35</span> <span class="keyword">rows</span>=<span class="number">1</span>)</span><br><span class="line">            -&gt; <span class="keyword">Table</span> <span class="keyword">scan</span> <span class="keyword">on</span> t1  (<span class="keyword">cost</span>=<span class="number">0.35</span> <span class="keyword">rows</span>=<span class="number">1</span>)</span><br></pre></td></tr></table></figure>
<p>在开启 Hash Join 后, MySQL 会尽量使用 Hash Join 以代替 BNLJ , 同时和 BNLJ 一样, 会受到 <code>join_buffer_size</code> 和 <code>open_files_limit</code> 的限制</p>
<p>更详细的内容可查阅 <a href="https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html" target="_blank" rel="noopener">官方文档</a></p>
<h2 id="Explain-analyze"><a href="#Explain-analyze" class="headerlink" title="Explain analyze"></a>Explain analyze</h2><p>最后谈一个 MySQL 8.0 的调试改动, MySQL 8.0.18 后引入了 <code>EXPLAIN ANALYZE</code> , 用于执行本次查询并输出执行计划, 内容如下所示:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">EXPLAIN</span> <span class="keyword">ANALYZE</span> <span class="keyword">SELECT</span> * <span class="keyword">FROM</span> t1 <span class="keyword">JOIN</span> t2 <span class="keyword">ON</span> (t1.c1 = t2.c2)\G</span><br><span class="line"></span><br><span class="line">*************************** <span class="number">1.</span> <span class="keyword">row</span> ***************************</span><br><span class="line"><span class="keyword">EXPLAIN</span>: -&gt; <span class="keyword">Inner</span> <span class="keyword">hash</span> <span class="keyword">join</span> (t2.c2 = t1.c1)  (<span class="keyword">cost</span>=<span class="number">4.70</span> <span class="keyword">rows</span>=<span class="number">6</span>)</span><br><span class="line">(actual <span class="built_in">time</span>=<span class="number">0.032</span>.<span class="number">.0</span><span class="number">.035</span> <span class="keyword">rows</span>=<span class="number">6</span> loops=<span class="number">1</span>)</span><br><span class="line">    -&gt; <span class="keyword">Table</span> <span class="keyword">scan</span> <span class="keyword">on</span> t2  (<span class="keyword">cost</span>=<span class="number">0.06</span> <span class="keyword">rows</span>=<span class="number">6</span>)</span><br><span class="line">(actual <span class="built_in">time</span>=<span class="number">0.003</span>.<span class="number">.0</span><span class="number">.005</span> <span class="keyword">rows</span>=<span class="number">6</span> loops=<span class="number">1</span>)</span><br><span class="line">    -&gt; <span class="keyword">Hash</span></span><br><span class="line">        -&gt; <span class="keyword">Table</span> <span class="keyword">scan</span> <span class="keyword">on</span> t1  (<span class="keyword">cost</span>=<span class="number">0.85</span> <span class="keyword">rows</span>=<span class="number">6</span>)</span><br><span class="line">(actual <span class="built_in">time</span>=<span class="number">0.018</span>.<span class="number">.0</span><span class="number">.022</span> <span class="keyword">rows</span>=<span class="number">6</span> loops=<span class="number">1</span>)</span><br></pre></td></tr></table></figure>
<ul>
<li>预估的执行成本(ms)以及返回行数</li>
<li>实际返回第一行前花费的时间, 返回所有行的时间</li>
<li>实际扫描行数, 循环数</li>
</ul>

  </article>
  <footer class="f-cf">
    
      <a href="/2019/11/06/page/" class="link f-fl">⟵</a>
    
    
      <a href="/2019/10/26/page/" class="link f-fr">TLS 证书申请⟶</a>
    
  </footer>
</section>
</div>
    <footer id="footer" class="f-cf">
  zhandequanla00@gmail.com
  
    
      
        · <a href="https://github.com/rarpainting" target="_blank" class="nav-icn">GitHub</a>
      
    
  
  <span class="copyright">All rights reserved @Rarpainting</span>
  <div>
    <div style="float: left">
      <div>
        <span style="margin-right: 20px">ICP主体备案号</span><span>粤ICP备17099046号</span>
      </div>
      <div>
        <span style="margin-right: 20px">网站备案号</span><span><a target="_blank" href="http://www.beian.miit.gov.cn">粤ICP备17099046号-1</a></span>
      </div>
    </div>
    <div style="float: right">
      <div>
        <span>Copyright</span>
        <span style="margin-right: 10px">©</span>
        <span>MyWeb詹</span>
      </div>
    </div>
  <div>
</div></div></footer>

  </div>
</body>
</html>